﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Configuration;
using Bestlife.ERP.DataAccess;
using System.Data;

/// <summary>
/// Summary description for inserts
/// </summary>
/// 

namespace Bestlife.ERP.BusinessRules
{
    public class inserts
    {
        Bestlife.ERP.Entities.E_Level_Genarator lvlgen = new Entities.E_Level_Genarator();
        Bestlife.ERP.Entities.E_Level_Genarator findnr = new Entities.E_Level_Genarator();
        List<string> childby_lvl = new List<string>();
        int Acount = 0;
        int Rcount = 0;
        int Dcount = 0;
        int SDcount = 0;
        int SEDcount = 0;
        int CDiamandcount = 0;


        Boolean chk =false;
        SqlDataReader rdRStatus;
        public inserts()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        #region Return Agents 

        public int return_Agents(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countAgent from Agent b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    Acount = int.Parse(rdRStatus["countAgent"].ToString());
                    //Acount = count;
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }

            return Acount;
        }

        #endregion

        #region Return Ruby

        public int return_Ruby(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countRuby from Ruby b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    Rcount = int.Parse(rdRStatus["countRuby"].ToString());
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }
            return Rcount;
        }

        #endregion

        #region Return Diamand

        public int return_Diamand(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countDiamond from Diamond b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    Dcount = int.Parse(rdRStatus["countDiamond"].ToString());
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }
            return Dcount;
        }

        #endregion

        #region Return SDiamand

        public int return_SDiamand(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countSD from SD b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    SDcount = int.Parse(rdRStatus["countDiamond"].ToString());
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }
            return SDcount;
        }

        #endregion

        #region Return SEDiamand

        public int return_SEDiamand(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countSED from SED b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    SEDcount = int.Parse(rdRStatus["countDiamond"].ToString());
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }
            return SEDcount;
        }

        #endregion

        #region Return CDiamand

        public int return_CDiamand(string lvlid)
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                String sqlstatus = "SELECT count(*) as countCD from C_Diamond b where b.Level_id LIKE '" + lvlid + "%'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    CDiamandcount = int.Parse(rdRStatus["countDiamond"].ToString());
                }


            }//try

            catch (Exception err)
            {

            }
            finally 
            {
                rdRStatus.Close();
            
            }
            return CDiamandcount;
        }

        #endregion


        public Boolean insert_stat(String ID, String level_id, String status)
        {

            String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
            Bestlife.ERP.DataAccess.clsCon.connect(connStr);

//////User_id	nchar(10)	Unchecked
//////level_id	nchar(10)	Checked
//////Agent_status	nchar(10)	Checked

            String Agent = "INSERT INTO Agent (User_id,level_id,Agent_status)VALUES('" + ID+ "','" + level_id + "','"+status+"')";
           // String crown = "INSERT INTO Crown (User_id,level_id,Crown_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";
            String Diamond = "INSERT INTO Diamond (User_id,level_id,Diamond_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";
            String Ruby = "INSERT INTO Ruby (User_id,level_id,Ruby_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";
            String SD = "INSERT INTO SD (User_id,level_id,SDiamond_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";
            String SED = "INSERT INTO SED (User_id,level_id,SED_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";
            String C_Diamond = "INSERT INTO C_Diamond (User_id,level_id,CD_status)VALUES('" + ID + "','" + level_id + "','" + status + "')";

            if (status.Equals("5"))
            {
                Boolean chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(Agent);
            }

            else if (status.Equals("6"))
            {
                chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(Ruby);
            }

            else if (status.Equals("7"))
            {
                chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(Diamond);
            }
            else if (status.Equals("8"))
            {
                chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(SD);
            }

            else if (status.Equals("9"))
            {
                chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(SED);
            }
            else if (status.Equals("10"))
            {
                chk = Bestlife.ERP.DataAccess.clsCon.AddEditDel(C_Diamond);
            }
          
            return chk;

        }

        #region Getchilds By Level
        public List<string> GetchildBy_Level(string lvlid) 
        {
            childby_lvl.Clear();//07-08-2012 clear childby_lvl list
            try
                  { //try
                      String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                      Bestlife.ERP.DataAccess.clsCon.connect(connStr);

                      String Ssql = "SELECT b.User_id from Distributor_Level b where b.Level_id LIKE '" + lvlid + "%'";
                      //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);                    
                      rdRStatus = clsCon.ReadTable(Ssql);


                     while(rdRStatus.Read())
                      {
                          childby_lvl.Add(rdRStatus["User_id"].ToString());
                      }

                  } //try

                  catch (Exception err)
                  {

                     // Response.Redirect("404.aspx?Massage=" + err.Message.ToString());
                  }

                    
                  finally
                   
                  {
                     clsCon.conn_new.Close();
                     rdRStatus.Close();

                  }   //finally

            return childby_lvl;
        }
        #endregion

        public Bestlife.ERP.Entities.E_Level_Genarator return_Dis_Level(string ID) 
        {

            try
            {//try

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);


                String sqlstatus = "SELECT * from Distributor_Level where User_id='" + ID + "'";

                rdRStatus = clsCon.ReadTable(sqlstatus);


                if (rdRStatus.Read())
                {
                    lvlgen.User_id = rdRStatus["User_id"].ToString();
                    lvlgen.Parent_to_user =rdRStatus["Parent_To_User"].ToString() ;
                    lvlgen.Level = rdRStatus["Level_id"].ToString();  
                }


            }//try

            catch (Exception err)
            {

            }
            finally
            {
                rdRStatus.Close();

            }
            return lvlgen;
        }

        public List<int> Findandreturn_St(string lvlid,string parent_st) 
        {

            Bestlife.ERP.BusinessRules.inserts ins = new Bestlife.ERP.BusinessRules.inserts();
            Bestlife.ERP.Entities.E_Level_Genarator elvl = new Bestlife.ERP.Entities.E_Level_Genarator();

            List<string> subchilstbylevel = new List<string>();
            List<int> ch_StList = new List<int>();

            subchilstbylevel.Clear();
            ch_StList.Clear();


            int agentcount = 0;
            int rubycount = 0;
            int diamandcount = 0;
            int sdiamandcount = 0;
            int sediamandcount = 0;
            int cdiamand = 0;

            
            //select childs using levelID
                    subchilstbylevel=GetchildBy_Level(lvlid);
                    foreach (string subid in subchilstbylevel) 
                    {
                     //getagent to crown diamand count
                        elvl = ins.return_Dis_Level(subid);
                        string Ch_level = elvl.Level;


                        agentcount += return_Agents(Ch_level);
                        rubycount += return_Ruby(Ch_level);
                        diamandcount += return_Diamand(Ch_level);
                        sdiamandcount += return_SDiamand(Ch_level);
                        sediamandcount += return_SEDiamand(Ch_level);
                        cdiamand += return_CDiamand(Ch_level);
                    }

                    ch_StList.Add(agentcount);
                    ch_StList.Add(rubycount);
                    ch_StList.Add(diamandcount);
                    ch_StList.Add(sdiamandcount);
                    ch_StList.Add(sediamandcount);
                    ch_StList.Add(cdiamand);

                    return ch_StList;

        }

    }
}